<?php
require_once(dirname(__FILE__) . '/../../inc/config.inc.php');
date_default_timezone_set('PRC');
require_once('PHPExcel.php');


$input_month_str =isset($_GET['m']) ? htmlspecialchars($_GET['m']) : "" ;

if( strtotime($input_month_str)<1575043200 ){
    exit("日期格式不正确");
}
$input_month = date("m", strtotime($input_month_str));

$today_date = date("Y年m月d日");




function getMonthweeks($date)
{
    $ret = array();
    $stimestamp = strtotime($date);
    //echo $stimestamp;
    $mdays = date('t', $stimestamp);
    $msdate = date('Y-m-d', $stimestamp); //月度开始日期
    //echo $msdate;
    $medate = date('Y-m-' . $mdays, $stimestamp); // 月度结束日期
    $etimestamp = strtotime($medate);
    //获取第一周
    $zcsy = 7 - date('w', $stimestamp); //第一周去掉第一天还有几天
    //echo $zcsy;
    $zcs1 = $msdate;
    //第一周结束的时间
    if( date('w', strtotime($msdate))==0){
        $zce1 = $msdate;
    }else{
        $zce1 = date('Y-m-d', strtotime("+$zcsy day", $stimestamp));
    }

    //echo $zce1;
    $first_week = ["第1周", $zcs1, $zce1];
    $first_week['data'] = getDaysList($zcs1, $zce1);
    //计算第一周里面的天数

    $ret[] = $first_week;
    //获取中间周次
    $jzc = 0;
    //获取月份是6周次还是5周次
    $jzc0 = 0;
    $jzc6 = 0;
    for ($i = $stimestamp; $i <= $etimestamp; $i += 86400) {
        // 0（表示星期天）到 6（表示星期六）
        if (date('w', $i) == 0) {
            $jzc0++;
        }
        if (date('w', $i) == 6) {
            $jzc6++;
        }
    }
    if ($jzc0 == 5 && $jzc6 == 5) {
        $jzc = 5;
    } else {
        $jzc = 4;
    }

    $t = strtotime('+1 monday ' . $msdate);
    $n = 1;


    if(date('w', $stimestamp)==1){
        for ($n = 2; $n <= $jzc; $n++) {
            $b = strtotime("+$n week -1 week", $t);
            $dsdate = date("Y-m-d", strtotime("0 day", $b));
            $dedate = date("Y-m-d", strtotime("6 day", $b));

            $item_week = ["第" . $n . "周", $dsdate, $dedate];
            $item_week['data'] = getDaysList($dsdate, $dedate);
            $ret[] = $item_week;

        }
    }else{
        for ($n = 1; $n < $jzc; $n++) {
            $b = strtotime("+$n week -1 week", $t);
            $dsdate = date("Y-m-d", strtotime("0 day", $b));
            $dedate = date("Y-m-d", strtotime("6 day", $b));

            $item_week = ["第" . ($n + 1) . "周", $dsdate, $dedate];
            $item_week['data'] = getDaysList($dsdate, $dedate);
            $ret[] = $item_week;

        }
    }


    //最后最后一周
    $zcsy = date('w', $etimestamp) - 1;//最後一周是周几 日~六 0~6
    if($zcsy>=0){
        $zcs1 = date('Y-m-d', strtotime("-$zcsy day", $etimestamp));
        $zce1 = $medate;
        $jzcz = $jzc + 1;
        $end_week = ["第" . $jzcz . "周", $zcs1, $zce1];
        $end_week['data'] = getDaysList($zcs1, $zce1);
        $ret[] = $end_week;
    }
    return $ret;
}


function getDaysList($startDate, $endDate)
{
    $item = [];
    $startDate = strtotime($startDate);
    $endDate = strtotime($endDate);
    $weekarray = array("日", "一", "二", "三", "四", "五", "六");
    for ($i = $startDate; $i <= $endDate; $i += 86400) {
        // 0（表示星期天）到 6（表示星期六）

        $item_day['day'] = date("Y-m-d", $i);
        $item_day['week_name'] = "星期" . $weekarray[date("w", $i)];
        $item[] = $item_day;
    }
    return $item;
}




$ret = getMonthweeks($input_month_str);



var_dump($ret);
exit;

//获取工程师列表
$manage_arr = [];
$dosql->Execute("SELECT * FROM  `#@__user`  WHERE `level`=3 and isreport=1  ORDER BY id asc", 8);
while ($row3 = $dosql->GetArray(8)) {
    $manage_arr[] = $row3;
}


$objPHPExcel = new PHPExcel();// 操作第一个工作表


$styleThinBlackBorderOutline = array(
    'borders' => array(
        'allborders' => array( //设置全部边框
            'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
        ),

    ),
);




$cellKey = array(
    'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
    'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
    'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
    'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
);


//月报表 表列说明
$cellName_end = array(
    'id' => array('序号', 'id', 6), //列名称，索引值，宽度
    'truename' => array('姓名', 'truename', 10), //列名称，索引值，宽度
);

// 循环周
$user_week_fee = [];
foreach ($ret as $key => $val) {

    $sheetname = "sheet" . $key;
    if ($key == 0) {
        //新建第一个工作表
        $sheetname = $objPHPExcel->getActiveSheet();
    } else {
        $sheetname = $objPHPExcel->createSheet();
    }


    $cellName_end[$val[0]] = [$val[0], "w" . $key, 12];

    $item = [];
    //循环 人
    $nn = 1;  //人员序号

    $week_total_fee=0; //本周所有人员的积分
    $user_count=0;
    $avg_item=[];

    foreach ($manage_arr as $kkk => $userid) {


        $new_item['id'] = $nn;
        $new_item['truename'] = $userid['truename'];
        $user_count++;


        $cellName = array(
            'id' => array('序号', 'id', 6), //列名称，索引值，宽度
            'truename' => array('姓名', 'truename', 10), //列名称，索引值，宽度
        );

        //循环 天
        $user_week_fee[$key][$kkk] = 0; // 周 ->用户   的 所有积分
        $week_fee = 0;
        foreach ($val['data'] as $kk => $vv) {

            $cellName[$vv['week_name']] = [$vv['week_name'], "d" . $kk, 12];

            /* echo $vv['day'] . "<br>";
             echo $vv['week_name'] . "<br>";*/


            $day_day = $vv['day'];
            $key_name = 'd' . $kk;

            $day_start = strtotime($day_day);
            $day_end = $day_start + 86399;
            $day_fee = 0;


            $uid = $userid['id'];
            $sql = "select op,fee from  `#@__fee_log` where manageid='$uid' and op_time>$day_start and op_time<$day_end ";
            //echo $sql;
            $dosql->Execute($sql, $uid);
            while ($row_fee = $dosql->GetArray($uid)) {
                //var_dump($row_fee);
                //echo "rrr" . $row_fee['fee'] . "<br>";
                if ($row_fee['op'] == "add") {
                    $day_fee += $row_fee['fee'];
                }
                if ($row_fee['op'] == "move") {
                    $day_fee -= $row_fee['fee'];
                }
            }
            $avg_item[$key_name] = '';
            $new_item[$key_name] = $day_fee;
            $week_total_fee+=$day_fee;

            $week_fee += $day_fee;
            $user_week_fee[$key][$kkk] += $day_fee;

        }

        $cellName['total'] = ['合计', 'total', 12];
        $cellName['remark'] = ['签字确认', 'remark', 12];

        $new_item['total'] = $week_fee;
        $new_item['remark'] = "";


        $nn++;

        $item[] = $new_item;



    }

    //获取平均分


    $avg_item['id'] = 0;
    $avg_item['truename'] = '平均分';
    $avg_item['total'] = round($week_total_fee/$user_count);
    $avg_item['remark'] = "";
    $item[] = $avg_item;


    //数组进行排序
    array_multisort(array_column($item,'total'),SORT_DESC,$item);

    //var_dump($cellName);

    // 设置第一行
    $sheetname->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
    $sheetname->mergeCells('A1:' . $cellKey[count($cellName) - 1] . '1');
    //合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
    $sheetname->setCellValue('A1', "服务管理虚拟收入明细表");
    $sheetname->getStyle('A1')->getFont()->setName('黑体')->setSize(14);
    $sheetname->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $sheetname->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $sheetname->getStyle('A1')->getAlignment()->setWrapText(true);

    // 设置第二行
    $sheetname->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
    $sheetname->mergeCells('A2:' . $cellKey[count($cellName) - 1] . '2');
    //合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
    $sheetname->setCellValue('A2', "部门：项目工程部                        日期：" . $today_date);

    $sheetname->getRowDimension('1')->setRowHeight(33);
    $sheetname->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $sheetname->getStyle('A2')->getAlignment()->setWrapText(true);
    $sheetname->getStyle('A2')->getFont()->setName('黑体')->setSize(12);
    $sheetname->getRowDimension('2')->setRowHeight(27);

    $topNumber = 3;//表头有几行占用


    $cellName_num = count($cellName);
    $lastCell = $cellKey[$cellName_num - 1]; //获取最后一行的 excel 字母


    $i = 0;
    //处理 表头
    foreach ($cellName as $k => $v) {
        $sheetname->setCellValue($cellKey[$i] . $topNumber, $v[0]);//设置表头数据

        $sheetname->getColumnDimension($cellKey[$i])->setWidth($v[2]);//设置列宽度
        //添加边框
        $sheetname->getStyle($cellKey[$i] . $topNumber)->applyFromArray($styleThinBlackBorderOutline);
        //设置字体 为10号

        // 设置所有垂直居中
        $sheetname->getStyle($cellKey[$i] . $topNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $sheetname->getRowDimension($topNumber)->setRowHeight(24);
        //设置字体 为10号 黑体
        $sheetname->getStyle($cellKey[$i] . $topNumber)->getFont()->setName('黑体')->setSize(12);

        // 居中
        $sheetname->getStyle($cellKey[$i] . $topNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $i++;
    }

    //处理数据
    foreach ($item as $k => $v) {
        // 当前行数值
        $now_line = $k + 1 + $topNumber;
        $ii = 0;
        foreach ($cellName as $k1 => $v1) {
            $sheetname->setCellValue($cellKey[$ii] . $now_line, $v[$v1[1]]);
            $sheetname->getStyle($cellKey[$cellName_num-2] . $now_line)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);// 设置文字颜色
            $ii++;
        }

        // 设置所有垂直居中
        $sheetname->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 居中
        $sheetname->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //添加边框
        $sheetname->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->applyFromArray($styleThinBlackBorderOutline);
        $sheetname->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getFont()->setName('黑体')->setSize(12);

        $sheetname->getRowDimension($now_line)->setRowHeight(27);



    }

    $sheetname->setTitle($input_month . "月份" . $val[0] . "报表");
}


//最后的报表

//新建 表 对 每周每个员工 积分进行合计
$objActSheet1 = $objPHPExcel->createSheet();


$cellName_end['total'] = ['合计', 'total', 10];
$cellName_end['remark'] = ['签字确认', 'remark', 16];

//循环 周
$week_fee = [];
$week_fee_list = [];
$uu=0;

$month_total_fee=0; //本周所有人员的积分
$month_user_count=0;
$month_avg_item=[];

foreach ($manage_arr as $kkk => $userid) {
    $week_fee['id']=$uu;
    $week_fee['truename']=$userid['truename'];
    $week_total=0;
    foreach ($ret as $key => $val) {

        $week_fee["w".$key] = $user_week_fee[$key][$kkk];
        $month_avg_item["w".$key] = '';
        //$week_fee["w".$key] = 0;
        $week_total+=$user_week_fee[$key][$kkk];
    }
    $week_fee['total']=$week_total;
    $week_fee['remark']="";
    $week_fee_list[]=$week_fee;

    $month_total_fee+=$week_total;

    $uu++;
}


$month_avg_item['id'] = 0;
$month_avg_item['truename'] = '平均分';
$month_avg_item['total'] = round($month_total_fee/$uu);
$month_avg_item['remark'] = "";
$week_fee_list[] = $month_avg_item;

array_multisort(array_column($week_fee_list,'total'),SORT_DESC,$week_fee_list);


// 设置第一行
$objActSheet1->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
$objActSheet1->mergeCells('A1:' . $cellKey[count($cellName_end) - 1] . '1');
//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
$objActSheet1->setCellValue('A1', $input_month."月份虚拟收入汇总报表");
$objActSheet1->getStyle('A1')->getFont()->setName('黑体')->setSize(14);
$objActSheet1->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet1->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objActSheet1->getStyle('A1')->getAlignment()->setWrapText(true);
$objActSheet1->getRowDimension('2')->setRowHeight(34);

// 设置第二行
$objActSheet1->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
$objActSheet1->mergeCells('A2:' . $cellKey[count($cellName_end) - 1] . '2');
//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
$objActSheet1->setCellValue('A2', "部门：项目工程部                                                     日期：" . $today_date);
$objActSheet1->getStyle('A2')->getFont()->setName('黑体')->setSize(12);
$objActSheet1->getRowDimension('2')->setRowHeight(27);
$objActSheet1->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet1->getStyle('A2')->getAlignment()->setWrapText(true);

$topNumber = 3;//表头有几行占用


$cellName_num = count($cellName_end);
$lastCell = $cellKey[$cellName_num - 1]; //获取最后一行的 excel 字母


$i = 0;
//处理 表头
foreach ($cellName_end as $k => $v) {

    $objActSheet1->setCellValue($cellKey[$i] . $topNumber, $v[0]);//设置表头数据
    $objActSheet1->getStyle($cellKey[$i] . $topNumber)->getFont()->setBold(true);//设置是否加粗
    $objActSheet1->getColumnDimension($cellKey[$i])->setWidth($v[2]);//设置列宽度
    //添加边框
    $objActSheet1->getStyle($cellKey[$i] . $topNumber)->applyFromArray($styleThinBlackBorderOutline);
    //设置字体 为10号
    $objActSheet1->getStyle($cellKey[$i] . $topNumber)->getFont()->setSize(10);

    $objActSheet1->getRowDimension($topNumber)->setRowHeight(24);
    //设置字体 为10号 黑体
    $objActSheet1->getStyle($cellKey[$i] . $topNumber)->getFont()->setName('黑体')->setSize(12);


    // 设置所有垂直居中
    $objActSheet1->getStyle($cellKey[$i] . $topNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

    // 居中
    $objActSheet1->getStyle($cellKey[$i] . $topNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $i++;
}

//处理数据
foreach ($week_fee_list as $k => $v) {
    // 当前行数值
    $now_line = $k + 1 + $topNumber;
    $ii = 0;
    foreach ($cellName_end as $k1 => $v1) {
        $objActSheet1->setCellValue($cellKey[$ii] . $now_line, $v[$v1[1]]);
        $objActSheet1->getStyle($cellKey[$cellName_num-2] . $now_line)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);// 设置文字颜色
        $ii++;
    }

    // 设置所有垂直居中

    $objActSheet1->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getAlignment()
        ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    // 居中
    $objActSheet1->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getAlignment()
        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //添加边框
    $objActSheet1->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->applyFromArray($styleThinBlackBorderOutline);

    $objActSheet1->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getFont()->setName('黑体')->setSize(12);

    $objActSheet1->getRowDimension($now_line)->setRowHeight(27);


}

//重命名表，这个名称是表格下方的名称
$objActSheet1->setTitle($input_month . "月份报表");

/*var_dump($cellName_end);
var_dump($week_fee_list);*/
//var_export($user_week_fee);



//设置活动单指数到第一个表,所以Excel打开时，是第一个表
$objPHPExcel->setActiveSheetIndex(0);

$xlsTitle = '奖励列表';
ob_end_clean();
//清除缓冲区,避免乱码
header('Content-type: application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
header("Content-Disposition: attachment; filename=$xlsTitle.xls");
header('Cache-Control: max-age=0');
//header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
//header ('Cache-Control: cache, must-revalidate');
header('Pragma: public');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');


exit
?>

